package com.bank.dao;

import com.bank.entity.DepositType;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DepositTypeDAO {

    // 增加存款类型
    public void addDepositType(DepositType type) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "INSERT INTO deposit (savingName, descrip) VALUES (?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, type.getSavingName());
        ps.setString(2, type.getDescript());
        ps.executeUpdate();

        // 关闭资源
        ps.close();
        conn.close();
    }

    // 查询所有存款类型
    public List<DepositType> getAllDepositTypes() throws SQLException {
        List<DepositType> list = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT * FROM deposit";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            DepositType type = new DepositType();
            type.setSavingID(rs.getInt("savingID"));
            type.setSavingName(rs.getString("savingName"));
            type.setDescript(rs.getString("descrip")); // 使用 descrip 列
            list.add(type);
        }

        // 关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return list;
    }

    // 修改存款类型
    public void updateDepositType(DepositType type) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "UPDATE deposit SET savingName=?, descrip=? WHERE savingID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, type.getSavingName());
        ps.setString(2, type.getDescript());
        ps.setInt(3, type.getSavingID());
        ps.executeUpdate();

        // 关闭资源
        ps.close();
        conn.close();
    }

    // 删除存款类型
    public void deleteDepositType(int savingID) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "DELETE FROM deposit WHERE savingID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, savingID);
        ps.executeUpdate();

        // 关闭资源
        ps.close();
        conn.close();
    }
//精确查询
    public DepositType getDepositTypeByID(int savingID) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        DepositType type = null;

        try {
            conn = DBUtil.getConnection();
            // 使用正确的表名 deposit
            String sql = "SELECT savingID, savingName, descrip FROM deposit WHERE savingID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, savingID);
            rs = ps.executeQuery();

            if (rs.next()) {
                type = new DepositType();
                type.setSavingID(rs.getInt("savingID"));
                type.setSavingName(rs.getString("savingName"));
                type.setDescript(rs.getString("descrip")); // 注意字段名是 descrip
            }
        } catch (SQLException ex) {
            // 添加详细的错误日志
            System.err.println("精确查询失败: " + ex.getMessage());
            System.err.println("SQL: " + ps.toString()); // 打印实际执行的SQL
            throw ex;
        } finally {
            // 确保资源被关闭
            try { if (rs != null) rs.close(); } catch (SQLException e) { /* 忽略 */ }
            try { if (ps != null) ps.close(); } catch (SQLException e) { /* 忽略 */ }
            try { if (conn != null) conn.close(); } catch (SQLException e) { /* 忽略 */ }
        }
        return type;
    }

    // 模糊查询：根据存款名称模糊查询
    public List<DepositType> getDepositTypesByNameLike(String namePattern) throws SQLException {
        List<DepositType> list = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT * FROM deposit WHERE savingName LIKE ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + namePattern + "%");
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            DepositType type = new DepositType();
            type.setSavingID(rs.getInt("savingID"));
            type.setSavingName(rs.getString("savingName"));
            type.setDescript(rs.getString("descrip"));
            list.add(type);
        }

        // 关闭资源
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    // 范围查询：根据存款ID范围查询
    public List<DepositType> getDepositTypesByIDRange(int startID, int endID) throws SQLException {
        List<DepositType> list = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT * FROM deposit WHERE savingID BETWEEN ? AND ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, startID);
        ps.setInt(2, endID);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            DepositType type = new DepositType();
            type.setSavingID(rs.getInt("savingID"));
            type.setSavingName(rs.getString("savingName"));
            type.setDescript(rs.getString("descrip"));
            list.add(type);
        }

        // 关闭资源
        rs.close();
        ps.close();
        conn.close();
        return list;
    }}
